package com.qimei.onerice.dao.impl;

import com.qimei.onerice.dao.CustomerDao;
import com.qimei.onerice.entity.Customer;
import com.qimei.onerice.until.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CustomerDaoImpl implements CustomerDao {

	private static final String INSERT_CUSTOMER_SQL = "INSERT INTO customers (customer_name, customer_username, customer_password, customer_email, customer_phone, customer_image) VALUES (?, ?, ?, ?, ?, ?)";
	private static final String LOGIN_CUSTOMER_SQL = "SELECT * FROM customers WHERE (customer_username = ? or customer_phone = ? or customer_email = ?) and customer_password=?";
	private static final String GET_CUSTOMER_BY_ID_SQL = "SELECT * FROM customers WHERE customer_id = ?";
	private static final String GET_ALL_CUSTOMERS_SQL = "SELECT * FROM customers";
	private static final String UPDATE_CUSTOMER_SQL = "UPDATE customers SET customer_name = ?, customer_username = ?, customer_password = ?, customer_email = ?, customer_phone = ?, customer_image = ? WHERE customer_id = ?";
	private static final String DELETE_CUSTOMER_SQL = "DELETE FROM customers WHERE customer_id = ?";
	private static final String CHECK_CUSTOMER_EXISTENCE_SQL = "SELECT COUNT(*) FROM customers WHERE customer_username = ? OR customer_email = ? OR customer_phone = ?";

	@Override
	public int addCustomer(Customer customer) {
		try (Connection connection = DBUtil.getConnection();
		     PreparedStatement preparedStatement = connection.prepareStatement(INSERT_CUSTOMER_SQL)) {
			preparedStatement.setString(1, customer.getCustomerName());
			preparedStatement.setString(2, customer.getCustomerUsername());
			preparedStatement.setString(3, customer.getCustomerPassword());
			preparedStatement.setString(4, customer.getCustomerEmail());
			preparedStatement.setString(5, customer.getCustomerPhone());
			preparedStatement.setString(6, customer.getCustomerImage());

			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return  0;
	}

	@Override
	public Customer customerLogin(String username, String password) {
		try (Connection connection = DBUtil.getConnection();
		     PreparedStatement preparedStatement = connection.prepareStatement(LOGIN_CUSTOMER_SQL)) {
			preparedStatement.setString(1, username);
			preparedStatement.setString(2, username);
			preparedStatement.setString(3, username);
			preparedStatement.setString(4, password);

			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				return createCustomerFromResultSet(resultSet);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public Customer getCustomerById(int customerId) {
		try (Connection connection = DBUtil.getConnection();
		     PreparedStatement preparedStatement = connection.prepareStatement(GET_CUSTOMER_BY_ID_SQL)) {
			preparedStatement.setInt(1, customerId);

			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				return createCustomerFromResultSet(resultSet);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Customer> getAllCustomers() {
		List<Customer> customers = new ArrayList<>();
		try (Connection connection = DBUtil.getConnection();
		     PreparedStatement preparedStatement = connection.prepareStatement(GET_ALL_CUSTOMERS_SQL)) {

			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				customers.add(createCustomerFromResultSet(resultSet));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return customers;
	}

	@Override
	public int updateCustomer(Customer customer) {
		try (Connection connection = DBUtil.getConnection();
		     PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_CUSTOMER_SQL)) {
			preparedStatement.setString(1, customer.getCustomerName());
			preparedStatement.setString(2, customer.getCustomerUsername());
			preparedStatement.setString(3, customer.getCustomerPassword());
			preparedStatement.setString(4, customer.getCustomerEmail());
			preparedStatement.setString(5, customer.getCustomerPhone());
			preparedStatement.setString(6, customer.getCustomerImage());
			preparedStatement.setInt(7, customer.getCustomerId());

			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public int deleteCustomer(int customerId) {
		try (Connection connection = DBUtil.getConnection();
		     PreparedStatement preparedStatement = connection.prepareStatement(DELETE_CUSTOMER_SQL)) {
			preparedStatement.setInt(1, customerId);

			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return  0;
	}

	@Override
	public boolean isCustomerExists(String username, String email, String phone) {
		try (Connection connection = DBUtil.getConnection();
		     PreparedStatement preparedStatement = connection.prepareStatement(CHECK_CUSTOMER_EXISTENCE_SQL)) {
			preparedStatement.setString(1, username);
			preparedStatement.setString(2, email);
			preparedStatement.setString(3, phone);

			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				int count = resultSet.getInt(1);
				return count > 0;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	private Customer createCustomerFromResultSet(ResultSet resultSet) throws SQLException {
		Customer customer = new Customer();
		customer.setCustomerId(resultSet.getInt("customer_id"));
		customer.setCustomerName(resultSet.getString("customer_name"));
		customer.setCustomerUsername(resultSet.getString("customer_username"));
		customer.setCustomerPassword(resultSet.getString("customer_password"));
		customer.setCustomerEmail(resultSet.getString("customer_email"));
		customer.setCustomerPhone(resultSet.getString("customer_phone"));
		customer.setCustomerImage(resultSet.getString("customer_image"));
		return customer;
	}
}
